package com.apikey.auth.repository;

import com.apikey.auth.entity.ApiKey;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * API Key数据访问层
 * 提供API Key的数据库操作方法
 *
 * @author API Key Framework
 * @version 1.0
 */
@Repository
public interface ApiKeyRepository extends JpaRepository<ApiKey, Long>, JpaSpecificationExecutor<ApiKey> {

    /**
     * 根据API Key值查找API Key（包含已删除）
     *
     * @param keyValue API Key值
     * @return API Key
     */
    Optional<ApiKey> findByKeyValue(String keyValue);

    /**
     * 根据API Key值查找API Key
     *
     * @param keyValue API Key值
     * @return API Key实体
     */
    Optional<ApiKey> findByKeyValueAndIsDeletedFalse(String keyValue);

    /**
     * 根据API Key值和删除状态查找API Key
     *
     * @param keyValue  API Key值
     * @param isDeleted 是否已删除
     * @return API Key
     */
    Optional<ApiKey> findByKeyValueAndIsDeleted(String keyValue, boolean isDeleted);

    /**
     * 根据名称查找API Key
     *
     * @param name API Key名称
     * @return API Key实体
     */
    Optional<ApiKey> findByNameAndIsDeletedFalse(String name);

    /**
     * 根据用户ID查找API Key列表
     *
     * @param userId   用户ID
     * @param pageable 分页参数
     * @return API Key分页结果
     */
    Page<ApiKey> findByUserIdAndIsDeletedFalse(String userId, Pageable pageable);

    /**
     * 根据状态查找API Key列表
     *
     * @param status   状态
     * @param pageable 分页参数
     * @return API Key分页结果
     */
    Page<ApiKey> findByStatusAndIsDeletedFalse(String status, Pageable pageable);

    /**
     * 查找所有未删除的API Key
     *
     * @param pageable 分页参数
     * @return API Key分页结果
     */
    Page<ApiKey> findByIsDeletedFalse(Pageable pageable);

    /**
     * 根据用户ID统计API Key数量
     *
     * @param userId 用户ID
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.userId = :userId AND a.isDeleted = false")
    long countByUserIdAndIsDeletedFalse(@Param("userId") String userId);

    /**
     * 根据状态统计API Key数量
     *
     * @param status 状态
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.status = :status AND a.isDeleted = false")
    long countByStatusAndIsDeletedFalse(@Param("status") String status);

    /**
     * 统计所有未删除的API Key数量
     *
     * @return API Key总数
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.isDeleted = false")
    long countByIsDeletedFalse();

    /**
     * 查找即将过期的API Key
     *
     * @param expiryTime 过期时间阈值
     * @return 即将过期的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.expiresAt <= :expiryTime AND a.status = 'ACTIVE' AND a.isDeleted = false")
    List<ApiKey> findExpiringApiKeys(@Param("expiryTime") LocalDateTime expiryTime);

    /**
     * 查找已过期的API Key
     *
     * @return 已过期的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.expiresAt < CURRENT_TIMESTAMP AND a.status = 'ACTIVE' AND a.isDeleted = false")
    List<ApiKey> findExpiredApiKeys();

    /**
     * 查找使用量超过限制的API Key
     *
     * @return 使用量超限的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE " +
            "(a.dailyLimit > 0 AND a.dailyUsage >= a.dailyLimit) OR " +
            "(a.monthlyLimit > 0 AND a.monthlyUsage >= a.monthlyLimit) " +
            "AND a.status = 'ACTIVE' AND a.isDeleted = false")
    List<ApiKey> findOverLimitApiKeys();

    /**
     * 根据权限查找API Key
     *
     * @param permission 权限
     * @return 拥有指定权限的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.permissions LIKE CONCAT('%', :permission, '%') AND a.isDeleted = false")
    List<ApiKey> findByPermissionContaining(@Param("permission") String permission);

    /**
     * 获取状态分布统计
     *
     * @return 状态分布Map，key为状态，value为数量
     */
    @Query("SELECT a.status, COUNT(a) FROM ApiKey a WHERE a.isDeleted = false GROUP BY a.status")
    List<Object[]> getStatusDistribution();

    /**
     * 获取每日创建统计
     *
     * @param days 统计天数
     * @return 每日创建统计列表
     */
    @Query(value = "SELECT DATE(created_at) as date, COUNT(*) as count FROM api_keys " +
            "WHERE is_deleted = false AND created_at >= :startDate " +
            "GROUP BY DATE(created_at) ORDER BY DATE(created_at)", nativeQuery = true)
    List<Object[]> getDailyCreationStats(@Param("startDate") LocalDateTime startDate);

    /**
     * 获取用户API Key统计
     *
     * @return 用户API Key统计列表
     */
    @Query("SELECT a.userId, COUNT(a) as count FROM ApiKey a " +
            "WHERE a.isDeleted = false AND a.userId IS NOT NULL " +
            "GROUP BY a.userId ORDER BY COUNT(a) DESC")
    List<Object[]> getUserApiKeyStats();

    /**
     * 查找最近创建的API Key
     *
     * @param limit 限制数量
     * @return 最近创建的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.isDeleted = false ORDER BY a.createdAt DESC")
    List<ApiKey> findRecentApiKeys(Pageable pageable);

    /**
     * 查找最近使用的API Key
     *
     * @param limit 限制数量
     * @return 最近使用的API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.isDeleted = false AND a.lastUsedAt IS NOT NULL ORDER BY a.lastUsedAt DESC")
    List<ApiKey> findRecentlyUsedApiKeys(Pageable pageable);

    /**
     * 根据名称模糊查询API Key
     *
     * @param name     名称关键字
     * @param pageable 分页参数
     * @return API Key分页结果
     */
    @Query("SELECT a FROM ApiKey a WHERE a.name LIKE CONCAT('%', :name, '%') AND a.isDeleted = false")
    Page<ApiKey> findByNameContainingAndIsDeletedFalse(@Param("name") String name, Pageable pageable);

    /**
     * 根据描述模糊查询API Key
     *
     * @param description 描述关键字
     * @param pageable    分页参数
     * @return API Key分页结果
     */
    @Query("SELECT a FROM ApiKey a WHERE a.description LIKE CONCAT('%', :description, '%') AND a.isDeleted = false")
    Page<ApiKey> findByDescriptionContainingAndIsDeletedFalse(@Param("description") String description, Pageable pageable);

    /**
     * 根据删除状态统计数量
     *
     * @param isDeleted 是否删除
     * @return 数量
     */
    long countByIsDeleted(boolean isDeleted);

    /**
     * 根据操作者ID和删除状态统计数量
     *
     * @param operatorId 操作者ID
     * @param isDeleted  是否删除
     * @return 数量
     */
    long countByOperatorIdAndIsDeleted(String operatorId, boolean isDeleted);

    /**
     * 根据ID和删除状态查找API Key
     *
     * @param id        ID
     * @param isDeleted 是否删除
     * @return API Key实体
     */
    Optional<ApiKey> findByIdAndIsDeleted(Long id, boolean isDeleted);

    /**
     * 根据时间范围查找API Key
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param pageable  分页参数
     * @return API Key分页结果
     */
    @Query("SELECT a FROM ApiKey a WHERE a.createdAt BETWEEN :startTime AND :endTime AND a.isDeleted = false")
    Page<ApiKey> findByCreatedAtBetweenAndIsDeletedFalse(@Param("startTime") LocalDateTime startTime,
                                                         @Param("endTime") LocalDateTime endTime,
                                                         Pageable pageable);

    /**
     * 检查API Key值是否已存在
     *
     * @param keyValue API Key值
     * @return 是否存在
     */
    @Query("SELECT COUNT(a) > 0 FROM ApiKey a WHERE a.keyValue = :keyValue AND a.isDeleted = false")
    boolean existsByKeyValueAndIsDeletedFalse(@Param("keyValue") String keyValue);

    /**
     * 检查API Key名称是否已存在
     *
     * @param name API Key名称
     * @return 是否存在
     */
    @Query("SELECT COUNT(a) > 0 FROM ApiKey a WHERE a.name = :name AND a.isDeleted = false")
    boolean existsByNameAndIsDeletedFalse(@Param("name") String name);

    /**
     * 根据创建时间范围统计API Key数量
     *
     * @param startTime 开始时间
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.createdAt >= :startTime AND a.isDeleted = false")
    long countByCreatedAtGreaterThanEqualAndIsDeletedFalse(@Param("startTime") LocalDateTime startTime);

    /**
     * 根据过期时间范围统计API Key数量
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.expiresAt BETWEEN :startTime AND :endTime AND a.isDeleted = false")
    long countByExpiresAtBetweenAndIsDeletedFalse(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 根据用户ID和状态统计API Key数量
     *
     * @param userId 用户ID
     * @param status 状态
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.userId = :userId AND a.status = :status AND a.isDeleted = false")
    long countByUserIdAndStatusAndIsDeletedFalse(@Param("userId") String userId, @Param("status") String status);

    /**
     * 根据创建时间范围统计API Key数量
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.createdAt BETWEEN :startTime AND :endTime AND a.isDeleted = false")
    long countByCreatedAtBetweenAndIsDeletedFalse(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 根据过期时间范围查找API Key
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.expiresAt BETWEEN :startTime AND :endTime AND a.isDeleted = false")
    List<ApiKey> findByExpiresAtBetweenAndIsDeletedFalse(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 根据过期时间查找已过期的API Key
     *
     * @param currentTime 当前时间
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.expiresAt < :currentTime AND a.isDeleted = false")
    List<ApiKey> findByExpiresAtLessThanAndIsDeletedFalse(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 根据用户ID和状态查找API Key
     *
     * @param userId 用户ID
     * @param status 状态
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.userId = :userId AND a.status = :status AND a.isDeleted = false")
    List<ApiKey> findByUserIdAndStatusAndIsDeletedFalse(@Param("userId") String userId, @Param("status") String status);

    /**
     * 根据用户ID查找API Key（不分页）
     *
     * @param userId 用户ID
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.userId = :userId AND a.isDeleted = false")
    List<ApiKey> findByUserIdAndIsDeletedFalse(@Param("userId") String userId);

    /**
     * 根据状态查找API Key（不分页）
     *
     * @param status 状态
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.status = :status AND a.isDeleted = false")
    List<ApiKey> findByStatusAndIsDeletedFalse(@Param("status") String status);

    /**
     * 查找所有未删除的API Key（不分页）
     *
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.isDeleted = false")
    List<ApiKey> findByIsDeletedFalse();

    /**
     * 根据删除状态和更新时间查找API Key
     *
     * @param cutoffTime 截止时间
     * @return API Key列表
     */
    @Query("SELECT a FROM ApiKey a WHERE a.isDeleted = true AND a.updatedAt < :cutoffTime")
    List<ApiKey> findByIsDeletedTrueAndUpdatedAtLessThan(@Param("cutoffTime") LocalDateTime cutoffTime);

    /**
     * 根据过期时间统计已过期的API Key数量
     *
     * @param currentTime 当前时间
     * @return 已过期的API Key数量
     */
    @Query("SELECT COUNT(a) FROM ApiKey a WHERE a.expiresAt < :currentTime AND a.isDeleted = false")
    long countByExpiresAtLessThanAndIsDeletedFalse(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 根据申请ID查找API Key
     *
     * @param applicationId 申请ID
     * @return API Key实体
     */
    Optional<ApiKey> findByApplicationIdAndIsDeletedFalse(Long applicationId);

    /**
     * 根据申请ID查找API Key列表
     *
     * @param applicationId 申请ID
     * @return API Key列表
     */
    List<ApiKey> findAllByApplicationIdAndIsDeletedFalse(Long applicationId);

    /**
     * 检查申请ID是否已有API Key
     *
     * @param applicationId 申请ID
     * @return 是否存在
     */
    @Query("SELECT COUNT(a) > 0 FROM ApiKey a WHERE a.applicationId = :applicationId AND a.isDeleted = false")
    boolean existsByApplicationIdAndIsDeletedFalse(@Param("applicationId") Long applicationId);

}